#!/usr/bin/python3
import psycopg2
 
#创建连接对象
conn=psycopg2.connect(database="iotbase",user="postgres",password="Xh123pswd",host="localhost",port="5432")
curdb=conn.cursor() #创建指针对象
 
 
# 获取结果
curdb.execute('SELECT uid,parent_id FROM areas')
results=curdb.fetchall()
amap = {}
for area in results:
    amap[area[0]] = {'uid':area[0],'parentId':area[1],'pathArray':[]}
    
for k in amap:
    cur = amap[k]
    
    #print(cur)
    cur['pathArray'].insert(0,k)
    parent = cur
    
    while True:
        parentid = parent['parentId']
        if parentid == 0:
            break
        cur['pathArray'].insert(0,parentid)
        parent = amap[parentid]
        if parent == None:
            break
        parentid = parent['parentId']
       
    
for k in amap:
    area = amap[k]
    sql = "update areas set path=" + "'[" + ','.join(str(i) for i in area['pathArray']) + "]' where uid=" + str(area['uid'])
    print(sql)
    curdb.execute(sql)
# 关闭练级
conn.commit()
curdb.close()
